"""
Excel输出工具模块 - 函数式设计
处理Excel文件的生成和格式化

作者: Claude
创建时间: 2024-09-14
"""

import os
from typing import List, Dict, Any, Tuple, Optional
from datetime import datetime
from dataclasses import dataclass
import pandas as pd


@dataclass
class ExcelColumn:
    """Excel列配置"""
    name: str           # 列名
    width: int = 15     # 列宽
    align: str = 'left' # 对齐方式
    format: Optional[str] = None  # 数据格式


@dataclass  
class ProcessingResult:
    """处理结果数据类"""
    original_filename: str
    processed_filename: str
    video_type: str
    extracted_id: str
    has_subtitle: bool = False
    cd_number: Optional[int] = None
    is_4k: bool = False
    is_valid: bool = True
    metadata: Optional[Dict[str, Any]] = None
    cleaned_filename: str = ""  # 清理后的文件名
    error_message: Optional[str] = None  # 错误信息
    correct_result: Optional[str] = None  # 正确结果（用于失败记录）


def create_basic_excel_columns() -> List[ExcelColumn]:
    """
    创建基础Excel列配置
    
    :return: Excel列配置列表
    """
    return [
        ExcelColumn(name="原始文件名", width=40, align='left'),
        ExcelColumn(name="处理后文件名", width=30, align='left'),
        ExcelColumn(name="视频类型", width=12, align='center'),
        ExcelColumn(name="提取ID", width=15, align='center'),
        ExcelColumn(name="字幕", width=8, align='center'),
        ExcelColumn(name="4K", width=8, align='center'),
        ExcelColumn(name="CD分段", width=10, align='center'),
        ExcelColumn(name="验证状态", width=10, align='center'),
        ExcelColumn(name="处理时间", width=18, align='center')
    ]


def create_extended_excel_columns() -> List[ExcelColumn]:
    """
    创建扩展Excel列配置（包含更多详细信息）
    
    :return: Excel列配置列表
    """
    return [
        ExcelColumn(name="序号", width=8, align='center'),
        ExcelColumn(name="原始文件名", width=45, align='left'),
        ExcelColumn(name="处理后文件名", width=35, align='left'),
        ExcelColumn(name="视频类型", width=12, align='center'),
        ExcelColumn(name="提取ID", width=18, align='center'),
        ExcelColumn(name="系列信息", width=12, align='center'),
        ExcelColumn(name="字幕", width=8, align='center'),
        ExcelColumn(name="4K质量", width=8, align='center'),
        ExcelColumn(name="CD分段", width=10, align='center'),
        ExcelColumn(name="验证状态", width=10, align='center'),
        ExcelColumn(name="质量信息", width=12, align='center'),
        ExcelColumn(name="日期信息", width=15, align='center'),
        ExcelColumn(name="处理时间", width=20, align='center'),
        ExcelColumn(name="备注", width=30, align='left')
    ]


def convert_results_to_dataframe(results: List[ProcessingResult], 
                                extended: bool = False) -> pd.DataFrame:
    """
    将处理结果转换为DataFrame
    
    :param results: 处理结果列表
    :param extended: 是否使用扩展格式
    :return: DataFrame
    """
    if not results:
        # 返回空DataFrame但包含列名
        columns = create_extended_excel_columns() if extended else create_basic_excel_columns()
        return pd.DataFrame(columns=[col.name for col in columns])
    
    data_rows = []
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    for idx, result in enumerate(results, 1):
        if extended:
            # 扩展格式数据
            row_data = {
                "序号": idx,
                "原始文件名": result.original_filename,
                "处理后文件名": result.processed_filename,
                "视频类型": result.video_type,
                "提取ID": result.extracted_id,
                "系列信息": extract_series_from_metadata(result.metadata),
                "字幕": "是" if result.has_subtitle else "否",
                "4K质量": "是" if result.is_4k else "否", 
                "CD分段": str(result.cd_number) if result.cd_number else "无",
                "验证状态": "通过" if result.is_valid else "失败",
                "质量信息": extract_quality_from_metadata(result.metadata),
                "日期信息": extract_date_from_metadata(result.metadata),
                "处理时间": current_time,
                "备注": generate_remarks(result)
            }
        else:
            # 基础格式数据
            row_data = {
                "原始文件名": result.original_filename,
                "处理后文件名": result.processed_filename,
                "视频类型": result.video_type,
                "提取ID": result.extracted_id,
                "字幕": "是" if result.has_subtitle else "否",
                "4K": "是" if result.is_4k else "否",
                "CD分段": str(result.cd_number) if result.cd_number else "无",
                "验证状态": "通过" if result.is_valid else "失败",
                "处理时间": current_time
            }
        
        data_rows.append(row_data)
    
    return pd.DataFrame(data_rows)


def extract_series_from_metadata(metadata: Optional[Dict[str, Any]]) -> str:
    """
    从元数据中提取系列信息
    
    :param metadata: 元数据字典
    :return: 系列信息
    """
    if not metadata:
        return ""
    
    return metadata.get('series_info', metadata.get('studio_info', ''))


def extract_quality_from_metadata(metadata: Optional[Dict[str, Any]]) -> str:
    """
    从元数据中提取质量信息
    
    :param metadata: 元数据字典
    :return: 质量信息
    """
    if not metadata:
        return ""
    
    return metadata.get('quality_info', '')


def extract_date_from_metadata(metadata: Optional[Dict[str, Any]]) -> str:
    """
    从元数据中提取日期信息
    
    :param metadata: 元数据字典
    :return: 日期信息
    """
    if not metadata:
        return ""
    
    # 尝试不同的日期字段
    date_fields = ['date_info', 'parsed_date']
    
    for field in date_fields:
        if field in metadata:
            date_value = metadata[field]
            if isinstance(date_value, dict) and 'date_string' in date_value:
                return date_value['date_string']
            elif isinstance(date_value, str):
                return date_value
    
    return ""


def generate_remarks(result: ProcessingResult) -> str:
    """
    生成备注信息
    
    :param result: 处理结果
    :return: 备注文本
    """
    remarks = []
    
    if not result.is_valid:
        remarks.append("验证失败")
    
    if result.metadata:
        # 添加特殊标记
        if result.metadata.get('series_priority', 0) > 80:
            remarks.append("热门系列")
        
        if result.metadata.get('studio_info'):
            remarks.append(f"制作商:{result.metadata['studio_info']}")
    
    return " | ".join(remarks)


def apply_excel_formatting(writer: pd.ExcelWriter, sheet_name: str, 
                          df: pd.DataFrame, columns: List[ExcelColumn]) -> None:
    """
    应用Excel格式化
    
    :param writer: Excel写入器
    :param sheet_name: 工作表名称
    :param df: DataFrame
    :param columns: 列配置
    """
    try:
        # 获取工作表和工作簿
        worksheet = writer.sheets[sheet_name]
        workbook = writer.book
        
        # 创建格式
        header_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'valign': 'top',
            'fg_color': '#D7E4BC',
            'border': 1
        })
        
        cell_format = workbook.add_format({
            'text_wrap': True,
            'valign': 'top',
            'border': 1
        })
        
        center_format = workbook.add_format({
            'text_wrap': True,
            'valign': 'top',
            'align': 'center',
            'border': 1
        })
        
        # 设置列宽和格式
        for idx, column in enumerate(columns):
            # 设置列宽
            worksheet.set_column(idx, idx, column.width)
            
            # 设置对齐格式
            if column.align == 'center':
                for row_idx in range(1, len(df) + 1):
                    worksheet.write(row_idx, idx, df.iloc[row_idx-1, idx], center_format)
            else:
                for row_idx in range(1, len(df) + 1):
                    worksheet.write(row_idx, idx, df.iloc[row_idx-1, idx], cell_format)
        
        # 格式化标题行
        for col_idx, column in enumerate(columns):
            worksheet.write(0, col_idx, column.name, header_format)
        
        # 冻结首行
        worksheet.freeze_panes(1, 0)
        
    except Exception as e:
        print(f"警告: Excel格式化失败: {e}")


def save_results_to_excel(results: List[ProcessingResult], 
                         output_file: str,
                         extended: bool = False,
                         include_summary: bool = True) -> None:
    """
    保存处理结果到Excel文件
    
    :param results: 处理结果列表
    :param output_file: 输出文件路径
    :param extended: 是否使用扩展格式
    :param include_summary: 是否包含汇总表
    """
    try:
        # 确保输出目录存在
        output_dir = os.path.dirname(output_file)
        if output_dir and not os.path.exists(output_dir):
            os.makedirs(output_dir)
        
        # 转换为DataFrame
        df = convert_results_to_dataframe(results, extended)
        columns = create_extended_excel_columns() if extended else create_basic_excel_columns()
        
        # 创建Excel写入器
        with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
            # 写入主数据表
            df.to_excel(writer, sheet_name='处理结果', index=False)
            apply_excel_formatting(writer, '处理结果', df, columns)
            
            # 如果需要，创建汇总表
            if include_summary and results:
                summary_df = create_summary_dataframe(results)
                summary_df.to_excel(writer, sheet_name='处理汇总', index=False)
                apply_summary_formatting(writer, '处理汇总', summary_df)
        
        print(f"Excel文件已保存到: {output_file}")
        print(f"包含 {len(results)} 条处理结果")
        
    except Exception as e:
        raise RuntimeError(f"保存Excel文件时出错: {e}")


def create_summary_dataframe(results: List[ProcessingResult]) -> pd.DataFrame:
    """
    创建汇总统计DataFrame
    
    :param results: 处理结果列表
    :return: 汇总DataFrame
    """
    # 统计数据
    total_files = len(results)
    valid_results = [r for r in results if r.is_valid]
    valid_count = len(valid_results)
    invalid_count = total_files - valid_count
    
    # 按类型统计
    type_counts = {}
    for result in valid_results:
        video_type = result.video_type
        type_counts[video_type] = type_counts.get(video_type, 0) + 1
    
    # 特殊统计
    subtitle_count = len([r for r in valid_results if r.has_subtitle])
    hd4k_count = len([r for r in valid_results if r.is_4k])
    cd_count = len([r for r in valid_results if r.cd_number])
    
    # 构建汇总数据
    summary_data = [
        {"统计项目": "总文件数", "数量": total_files, "占比": "100.0%"},
        {"统计项目": "成功处理", "数量": valid_count, "占比": f"{valid_count/total_files*100:.1f}%"},
        {"统计项目": "处理失败", "数量": invalid_count, "占比": f"{invalid_count/total_files*100:.1f}%"},
        {"统计项目": "", "数量": "", "占比": ""},  # 分割线
        {"统计项目": "按类型统计", "数量": "", "占比": ""},
    ]
    
    # 添加类型统计
    for video_type, count in type_counts.items():
        type_name = {"FC2": "FC2类型", "CENSORED": "有码", "UNCENSORED": "无码"}.get(video_type, video_type)
        percentage = f"{count/valid_count*100:.1f}%" if valid_count > 0 else "0%"
        summary_data.append({"统计项目": f"  {type_name}", "数量": count, "占比": percentage})
    
    # 添加特殊统计
    summary_data.extend([
        {"统计项目": "", "数量": "", "占比": ""},  # 分割线
        {"统计项目": "特殊统计", "数量": "", "占比": ""},
        {"统计项目": "  包含字幕", "数量": subtitle_count, "占比": f"{subtitle_count/valid_count*100:.1f}%" if valid_count > 0 else "0%"},
        {"统计项目": "  4K质量", "数量": hd4k_count, "占比": f"{hd4k_count/valid_count*100:.1f}%" if valid_count > 0 else "0%"},
        {"统计项目": "  分段视频", "数量": cd_count, "占比": f"{cd_count/valid_count*100:.1f}%" if valid_count > 0 else "0%"},
    ])
    
    return pd.DataFrame(summary_data)


def apply_summary_formatting(writer: pd.ExcelWriter, sheet_name: str, df: pd.DataFrame) -> None:
    """
    应用汇总表格式化
    
    :param writer: Excel写入器
    :param sheet_name: 工作表名称
    :param df: DataFrame
    """
    try:
        worksheet = writer.sheets[sheet_name]
        workbook = writer.book
        
        # 创建格式
        header_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'valign': 'top',
            'fg_color': '#4F81BD',
            'font_color': 'white',
            'border': 1
        })
        
        category_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'valign': 'top',
            'fg_color': '#D9E1F2',
            'border': 1
        })
        
        data_format = workbook.add_format({
            'text_wrap': True,
            'valign': 'top',
            'border': 1
        })
        
        # 设置列宽
        worksheet.set_column(0, 0, 20)  # 统计项目
        worksheet.set_column(1, 1, 12)  # 数量
        worksheet.set_column(2, 2, 12)  # 占比
        
        # 格式化标题行
        for col_idx, col_name in enumerate(df.columns):
            worksheet.write(0, col_idx, col_name, header_format)
        
        # 格式化数据行
        for row_idx in range(len(df)):
            row_data = df.iloc[row_idx]
            
            # 判断是否为分类行
            if row_data['统计项目'] in ['按类型统计', '特殊统计'] or row_data['统计项目'] == '':
                format_to_use = category_format
            else:
                format_to_use = data_format
            
            for col_idx, value in enumerate(row_data):
                worksheet.write(row_idx + 1, col_idx, value, format_to_use)
        
        # 冻结首行
        worksheet.freeze_panes(1, 0)
        
    except Exception as e:
        print(f"警告: 汇总表格式化失败: {e}")


def export_to_csv(results: List[ProcessingResult], output_file: str) -> None:
    """
    导出结果到CSV文件 - 简单版本
    
    :param results: 处理结果列表
    :param output_file: 输出文件路径
    """
    try:
        # 创建简单的DataFrame
        data = []
        for result in results:
            data.append({
                '原始文件名': result.original_filename,
                '处理后文件名': result.processed_filename
            })
        
        df = pd.DataFrame(data)
        df.to_csv(output_file, index=False, encoding='utf-8-sig')
        
        print(f"CSV文件已保存到: {output_file}")
        
    except Exception as e:
        raise RuntimeError(f"保存CSV文件时出错: {e}")


# 导出的函数式接口
__all__ = [
    "save_results_to_excel",
    "export_to_csv", 
    "convert_results_to_dataframe",
    "create_summary_dataframe",
    "ProcessingResult",
    "ExcelColumn"
]